package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import com.sds.fw.dao.AbDao;
import com.sds.shop.vo.MemVO;

public class MemDAO extends AbDao {
	private static MemDAO dao = new MemDAO();
	public static MemDAO getInstance(){
		return dao;
	}
	
	public MemVO getCustomer(String customerId){
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();
			
			StringBuffer query = new StringBuffer();
			query.append("SELECT mem_id, mem_nm, mem_cd, pwd, pwd_hint, pwd_hint_ans, ssn, birth_dt, ");
			query.append("        male_flg, tel_no, cell_no, addr, email, email_rcv_flg, mem_reg_dtm ");
			query.append("FROM tb_mem ");
			query.append("WHERE mem_id = ? ");
						
			ps = conn.prepareStatement( query.toString() );
			ps.setString(1, customerId);
			rs = ps.executeQuery();
			while(rs.next()){
				MemVO vo = new MemVO();
				
			    vo.setMemId(rs.getString(1));
			    vo.setMemNm(rs.getString(2));
			    vo.setMemCd(rs.getString(3));
			    vo.setPwd(rs.getString(4));
			    vo.setPwdHint(rs.getString(5));
			    vo.setPwdHintAns(rs.getString(6));
			    vo.setSsn(rs.getString(7));
			    vo.setBirthDt(rs.getString(8));
			    vo.setMaleFlg(rs.getString(9));
			    vo.setTelNo(rs.getString(10));
			    vo.setCellNo(rs.getString(11));
			    vo.setAddr(rs.getString(12));
			    vo.setEmail(rs.getString(13));
			    vo.setEmailRcvFlg(rs.getString(14));
			    vo.setMemRegDtm(rs.getString(15));
			    close(conn, ps, rs);
				return vo;
			}
		}catch(Exception ex){
			System.out.println("getCustomer exception : " + ex);
			close(conn, ps, rs);
		}
		
		return null;
	} // getCustomer
	
	public MemVO getCustomer(String memId, String pwd){
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();
			
			StringBuffer query = new StringBuffer();
			query.append("SELECT mem_id, mem_nm, mem_cd, pwd, pwd_hint, pwd_hint_ans, ssn, birth_dt, ");
			query.append("        male_flg, tel_no, cell_no, addr, email, email_rcv_flg, mem_reg_dtm ");
			query.append("FROM tb_mem ");
			query.append("WHERE mem_id = ? ");
			query.append("AND   pwd = ? ");
						
			ps = conn.prepareStatement( query.toString() );
			ps.setString(1, memId);
			ps.setString(2, pwd);
			rs = ps.executeQuery();
			while(rs.next()){
				MemVO vo = new MemVO();
				
			    vo.setMemId(rs.getString(1));
			    vo.setMemNm(rs.getString(2));
			    vo.setMemCd(rs.getString(3));
			    vo.setPwd(rs.getString(4));
			    vo.setPwdHint(rs.getString(5));
			    vo.setPwdHintAns(rs.getString(6));
			    vo.setSsn(rs.getString(7));
			    vo.setBirthDt(rs.getString(8));
			    vo.setMaleFlg(rs.getString(9));
			    vo.setTelNo(rs.getString(10));
			    vo.setCellNo(rs.getString(11));
			    vo.setAddr(rs.getString(12));
			    vo.setEmail(rs.getString(13));
			    vo.setEmailRcvFlg(rs.getString(14));
			    vo.setMemRegDtm(rs.getString(15));
			    close(conn, ps, rs);
				return vo;
			}
			
		}catch(Exception ex){
			System.out.println("getCustomer exception : " + ex);
			close(conn, ps, rs);
		}
		
		return null;
	} // getCustomer
	
	
	public boolean setCustomer(String mem_id, String mem_nm, String mem_cd, String pwd, String pwd_hint, String pwd_hint_ans,
			String ssn, String birth_dt, String male_flg, String tel_no, String cell_no, String addr,
			String email, String email_rcv_flg){
	
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();
			
			
			StringBuffer query = new StringBuffer();
			query.append("INSERT INTO TB_MEM VALUES (?, ?, ?, ?, ?, ?, ?, ?, ");
			query.append("?, ?, ?, ?, ?, ?, to_char(sysdate, 'yyyymmddhhmmss')) ");

			PreparedStatement pt = conn.prepareStatement(query.toString());
			pt.setString(1, mem_id);
			pt.setString(2, mem_nm);
			pt.setString(3, mem_cd);
			pt.setString(4, pwd);
			pt.setString(5, pwd_hint);
			pt.setString(6, pwd_hint_ans);
			pt.setString(7, ssn);
			pt.setString(8, birth_dt);
			pt.setString(9, male_flg);
			pt.setString(10, tel_no);
			pt.setString(11, cell_no);
			pt.setString(12, addr);
			pt.setString(13, email);
			pt.setString(14, email_rcv_flg);
			
			int index = pt.executeUpdate();
			
			if(index < 1){
				return false;
			}
			
			close(conn, ps, rs);
			
		}catch (Exception ex) {
			System.out.println("setCustomer exception : " + ex);
			close(conn, ps);
			return false;
		}
		
		return true;
	}
	
	
	public boolean updateCustomer(String mem_id, String mem_nm, String mem_cd, String pwd, String pwd_hint, String pwd_hint_ans,
			String ssn, String birth_dt, String male_flg, String tel_no, String cell_no, String addr,
			String email, String email_rcv_flg){
	
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();
			
			
			StringBuffer query = new StringBuffer();
			query.append("UPDATE TB_MEM SET mem_nm=?, mem_cd=?, pwd=?, pwd_hint=?, pwd_hint_ans=?, ssn=?, birth_dt=?, male_flg=?, tel_no=?, cell_no=?, addr=?, ");
			query.append("email=?, email_rcv_flg=? where mem_id = ?");

			PreparedStatement pt = conn.prepareStatement(query.toString());
			
			pt.setString(1, mem_nm);
			pt.setString(2, mem_cd);
			pt.setString(3, pwd);
			pt.setString(4, pwd_hint);
			pt.setString(5, pwd_hint_ans);
			pt.setString(6, ssn);
			pt.setString(7, birth_dt);
			pt.setString(8, male_flg);
			pt.setString(9, tel_no);
			pt.setString(10, cell_no);
			pt.setString(11, addr);
			pt.setString(12, email);
			pt.setString(13, email_rcv_flg);
			pt.setString(14, mem_id);
			
			int index = pt.executeUpdate();
			
			if(index < 1){
				return false;
			}
			close(conn, ps, rs);
			
			
		}catch (Exception ex) {
			System.out.println("updateCustomer exception : " + ex);
			close(conn, ps);
			return false;
		}
		
		return true;
	}
	
	public Vector<MemVO> getListMember(){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Vector<MemVO> list = new Vector<MemVO>();
		try{
			conn = connect();
			
			StringBuffer query = new StringBuffer();
			query.append("SELECT mem_id, mem_nm, mem_cd, pwd, pwd_hint, pwd_hint_ans, ssn, birth_dt, ");
			query.append("        male_flg, tel_no, cell_no, addr, email, email_rcv_flg, mem_reg_dtm ");
			query.append("FROM tb_mem ");
						
			ps = conn.prepareStatement( query.toString() );
			
			rs = ps.executeQuery();
			while(rs.next()){
				MemVO vo = new MemVO();
				
			    vo.setMemId(rs.getString(1));
			    vo.setMemNm(rs.getString(2));
			    vo.setMemCd(rs.getString(3));
			    vo.setPwd(rs.getString(4));
			    vo.setPwdHint(rs.getString(5));
			    vo.setPwdHintAns(rs.getString(6));
			    vo.setSsn(rs.getString(7));
			    vo.setBirthDt(rs.getString(8));
			    vo.setMaleFlg(rs.getString(9));
			    vo.setTelNo(rs.getString(10));
			    vo.setCellNo(rs.getString(11));
			    vo.setAddr(rs.getString(12));
			    vo.setEmail(rs.getString(13));
			    vo.setEmailRcvFlg(rs.getString(14));
			    vo.setMemRegDtm(rs.getString(15));
			    
				list.add(vo);
			}
			close(conn, ps, rs);
		}catch(Exception ex){
			System.out.println("getListMember exception : " + ex);
			close(conn, ps, rs);
		}
		
		return list;
	} // getCustomer
	
}

